A portion of the disclosure of this patent document contains material which is subject to copyright protection. The copyright owner has no objection to the facsimile reproduction by anyone of the patent document or the patent disclosure, as it appears in the Patent and Trademark Office patent file or records, but otherwise reserves all copyright rights whatsoever.
1. The Field of the Invention
The present invention relates generally to computer-implemented database systems. More specifically, the present invention relates to an apparatus and method for dynamically generating query explain data with a database system.
2. The Relevant Technology
Databases are computerized information storage and retrieval systems. Databases are managed by systems and may take the form of relational databases and hierarchical databases. A Relational Database Management System (RDBMS) is a database system which uses relational techniques for storing and retrieving data. Relational databases are organized into tables consisting of rows (tuples) and columns of data. A relational database typically includes many tables, and each table includes multiple rows and columns. The tables are conventionally stored in direct access storage devices (DASD), such as magnetic or optical disk drives, for semi-permanent storage.
Generally, users communicate with an RDBMS using a Structured Query Language (SQL) interface. The SQL interface allows users to create, manipulate, and query a database by formulating relational operations on the tables, either interactively, in batch files, or embedded in host languages such as C and COBOL. SQL has evolved into a standard language for RDBMS software and has been adopted as such by both the American National Standards Institute (ANSI) and the International Standards Organization (ISO).
The SQL standard provides that each RDBMS should respond to a particular query in the same way, regardless of the underlying database. However, the method that the RDBMS actually uses to find the requested information in the database is left to the RDBMS. Typically, the RDBMS is capable of accessing the requested data in a number of different manners. The RDBMS, therefore, attempts to select the manner that minimizes the computer time and resources (i.e. cost) for executing the query.
When the RDBMS determines how to execute the SQL statements, the set of steps created by the RDBMS for executing the SQL statements is commonly referred to as the xe2x80x9caccess path.xe2x80x9d In other words, the access path is a sequence of operations used by the RDBMS to obtain the data requested by the SQL query. Depending on the access path, an SQL statement might, for instance, search an entire table space, or it might use an index. The access path is a key to determining how well an SQL statement performs. The description of the access path is stored in a table often referred to as a xe2x80x9cplan table,xe2x80x9d which typically stores the access path data for one or more SQL statements.
In addition to determining the access path many databases estimate the cost (in time or service units) for executing each SQL statement. Typically, the estimated costs are stored in a table, referred to in the case of DB2(copyright) for OS/390(copyright) as a xe2x80x9cstatement table.xe2x80x9d Like the plan table, the statement table generally stores the estimated statement costs for one or more SQL statements.
Moreover, some databases store information relating to user-defined functions in a table often referred to as a xe2x80x9cfunction table.xe2x80x9d User-defined functions can be very useful in developing database applications. Accordingly, it is advantageous to have information relating to the user-defined functions in a single, convenient location.
Collectively, the above-described access path data, statement cost data, and function data are referred to herein as xe2x80x9cexplain data.xe2x80x9d The plan table, statement table, and function table are referred to herein as xe2x80x9cexplain tables.xe2x80x9d Many database systems, such as the RDBMS, provide a query explain program which is used to access the explain data for user-selected queries. The query explain programs are in some instances configured to provide the information in a graphical manner, or in a manner otherwise readily comprehendible to a user.
While the explain data is typically generated at bind time, the explain data can also be generated dynamically in response to a user-supplied query statement. The user can alter the queries submitted to dynamically modify or enter SQL statements and quickly see the results.
To make this capability even more powerful, it has been found beneficial by the inventors to be able to control as many parameters of the explain function as possible in order to better customize the received results, and thereby better analyze a given query for efficiency and effectiveness. Additionally, the inventors have found it beneficial to be able to enter the SQL query for which explain data is desired to be generated from a remote location, and from a remote program, and be able to control the RDBMS (or other database system) parameters for the specific query being entered from the remote program.
Accordingly, it would be beneficial to provide a system, method, and article of manufacture for customizing queries remotely and for generating query explain data for the queries remotely on the database system. It would be even more beneficial if such a system were provided in which system parameters and defaults, such as database system settings, could be set on a case by case basis from the remote program for a selected query.
The present invention solves the foregoing problems by providing a system, method, and article of manufacture for dynamically generating query explain data. In one aspect of the invention, an apparatus includes a processor for executing instructions and a memory device having thereon modules of operational data and executable code for execution by the processor.
In one embodiment, the modules include a query explain program configured to operate on a first computer station communicating over the network with a second computer station hosting the database system. Preferably, the query explain program is also configured to request generation of query explain data by the database system over the network. A query identification module is preferably adapted to communicate with the query for which the user wishes the database system to generate the query explain data. A system settings module is preferably configured to communicate with the query explain program and to receive a user designation of one or more database system settings with which the user wishes the database system to comply when generating the query explain data.
In one embodiment, the second computer station comprises a server and the first computer station preferably comprises a work station attached to the server. The system setting may comprise a default query execution setting.
The query explain program may include a graphical user interface (GUI) configured to be displayed on an output device connected to the first computer station. Preferably, the GUI window comprises user controls for receiving the user designation and configuration of the system settings for which the user wishes the database system to comply for execution of query execution data for the selected query.
In one embodiment, the GUI window comprises a SQL query text editor configured to receive the designation of the selected query from a user. The GUI window may additionally, or alternatively, comprise a SQL query designation module configured to allow a user to designate the selected query from among a plurality of existing queries previously generated for execution by the database system.
The controls may comprise a pull down window with which the user may configure the system settings. In one embodiment, the system setting comprises a degree of parallel processing with which the database system is to execute the selected query. Other system settings for which the user may designate on a case by case basis for the generation of query execution data include the current SQL ID, which identifies the user ID that owns resources referenced during query execution. Preferably, the system settings module is configured to alter the system setting for only the selected query, and to return the system setting back to the previous configuration after query execution data for the selected query has been generated.
In another aspect of the invention, a method for dynamically generating query explain data is provided and includes a step of providing a query explain program configured to operate on a first computer station communicating over the network with a second computer station hosting the database system. A further step may comprise receiving user input within the query explain program regarding a selected query for which the user wishes the database system to generate query explain data. Additionally, the method may comprise receiving a user designation within the query explain program of a database system setting with which the user wishes the database system to comply when generating the query explain data.
In one embodiment, the second computer station comprises a server, and the first computer station comprises a work station attached to the server, and the system setting comprises a default query execution setting.
Additional steps of the method may comprise providing a graphical user interface (GUI) window accessible through the query explain program. Preferably, the GUI window is configured to be displayed on an output device connected to the first computer station. The method also preferably comprises providing user controls for configuring the system setting. The user controls are preferably accessible through the GUI window.
The method in one embodiment also comprises receiving by the query explain program the selected query from a user. The query may be typed by a user, read from a file, or inserted by means of a paste text file function, or the method may comprise providing to the user a listing of a plurality of existing queries previously generated for execution by the database system and receiving from the user a designation of the selected query from among the plurality of existing queries.
The method in one embodiment also comprises receiving a user designation of one or more system settings. In one embodiment, this comprises providing a pull down window configured to receive the user designation of the system setting.
In one embodiment, the system setting comprises a degree of parallel processing with which the database system is to execute the selected query. Other settings which may be selected by the user for executing query execution data for a selected query comprise the current SQL ID. Preferably, the database system setting is altered in accordance with the user designation for only the selected query, and is returned to its original value after the query execution data has been generated for the selected query.
In yet another aspect of the invention, an article of manufacture comprises a program storage medium readable by a processor and embodying one or more instructions executable by the processor to perform the above-described method of locally caching query execution data received across a network from a database system.
These and other objects, features, and advantages of the present invention will become more fully apparent from the following description and appended claims, or may be learned by the practice of the invention as set forth in the following specification.